 CREATE TABLE LOCATION
	(LOCATIONID integer,
	CITY varchar(30),
	STATE varchar(30),
	COUNTRY varchar(30),
	PRIMARY KEY(LOCATIONID));

CREATE SEQUENCE loc_sequence
START WITH 1
INCREMENT BY 1;

CREATE TRIGGER loc_trigger
BEFORE INSERT ON LOCATION
FOR EACH ROW
BEGIN
SELECT loc_sequence.nextval into :new.LOCATIONID from dual;
END;
.
RUN;

CREATE TABLE INSTITUTION
	(INSTITUTIONID integer,
	NAME varchar(200),	
	CONCENTRATION varchar(200),
	DEGREE varchar(200),
	YEAR_OF_GRADUATION integer,
	PRIMARY KEY(INSTITUTIONID));

CREATE SEQUENCE inst_sequence
START WITH 1
INCREMENT BY 1;

CREATE TRIGGER inst_trigger
BEFORE INSERT ON INSTITUTION
FOR EACH ROW
BEGIN
SELECT inst_sequence.nextval into :new.INSTITUTIONID from dual;
END;
.
RUN;


CREATE TABLE PROFILE
	(PROFILEID integer,
	FIRST_NAME varchar(200),
	LAST_NAME varchar(200),
	YEAR_OF_BIRTH smallint,
	MONTH_OF_BIRTH smallint,
	DAY_OF_BIRTH smallint,
	GENDER varchar(10),
	CURRENT_LOCATIONID integer,
	HOMETOWNID integer,
	PRIMARY KEY(PROFILEID),
	FOREIGN KEY(CURRENT_LOCATIONID) REFERENCES LOCATION,
	FOREIGN KEY(HOMETOWNID) REFERENCES LOCATION);

CREATE TABLE ATTENDS
	(PROFILEID integer,
	INSTITUTIONID integer,
	PRIMARY KEY(PROFILEID, INSTITUTIONID),
	FOREIGN KEY(PROFILEID) REFERENCES PROFILE,
	FOREIGN KEY(INSTITUTIONID) REFERENCES INSTITUTION);

CREATE TABLE FRIENDSHIP
	(USER_1 integer,
	USER_2 integer,
	PRIMARY KEY(USER_1, USER_2),
	FOREIGN KEY(USER_1) REFERENCES PROFILE,
	FOREIGN KEY(USER_2) REFERENCES PROFILE);

CREATE TABLE PHOTO
	(PHOTOID integer,
	CAPTION varchar(200),
	LINK varchar(200),
	MODIFIED_TIME DATE,
	CREATED_TIME DATE,
	ALBUM_ID integer,
	PRIMARY KEY(PHOTOID));

CREATE TABLE ALBUM
	(ALBUMID integer,
	OWNERID integer NOT NULL,
	ALBUM_NAME varchar(200),
	CREATED_TIME DATE,
	MODIFIED_TIME DATE,
	LINK varchar(200),
	VISIBILITY smallint,
	COVER_PHOTO_ID integer NOT NULL,
	PRIMARY KEY(ALBUMID),
	FOREIGN KEY(OWNERID) REFERENCES PROFILE);

ALTER TABLE PHOTO
ADD CONSTRAINT ALBUM_ID_ FOREIGN KEY(ALBUM_ID) REFERENCES ALBUM(ALBUMID)
INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE ALBUM
ADD CONSTRAINT COVER_PHOTO_ID_ FOREIGN KEY(COVER_PHOTO_ID) REFERENCES PHOTO(PHOTOID)
INITIALLY DEFERRED DEFERRABLE;


CREATE TABLE EVENT
	(EVENTID integer,
	CREATORID integer NOT NULL,
	NAME varchar(200),
	TAGLINE varchar(200),
	DESCRIPTION varchar(200),
	HOST varchar(200),
	EVENT_TYPE varchar(200),
	EVENT_SUBTYPE varchar(200),
	LOCATIONID integer,
	EVENT_LOCATION_NAME varchar(100),
	START_TIME DATE,
	END_TIME DATE,
	PRIMARY KEY(EVENTID),
	FOREIGN KEY(CREATORID) REFERENCES PROFILE,
	FOREIGN KEY(LOCATIONID) REFERENCES LOCATION);


CREATE TABLE PARTICIPATING
	(STATUS smallint,
	PROFILEID integer,
	EVENTID integer,
	PRIMARY KEY(PROFILEID, EVENTID),
	FOREIGN KEY(PROFILEID) REFERENCES PROFILE,
	FOREIGN KEY(EVENTID) REFERENCES EVENT);

CREATE TABLE TAG
	(PROFILEID integer,
	PHOTOID integer,
	X_COORDINATE integer,
	Y_COORDINATE integer,
	CREATED_TIME DATE,
	PRIMARY KEY(PROFILEID, PHOTOID),
	FOREIGN KEY(PROFILEID) REFERENCES PROFILE,
	FOREIGN KEY(PHOTOID) REFERENCES PHOTO);

